-- @author lixiyong01
-- @date 2023.03.18
-- 表负责人与任务负责人进行映射
use hdp_lbg_supin_zplisting;

insert overwrite table ads_zp_rock_husky_owner_check partition(dt = '${dateSuffix}')
select a.table_id, a.database_name, a.table_name, a.owner_id, b.task_id, b.task_name, b.queue_name, b.owner_id,
    a.is_partition, a.lifecycle, a.filesize, a.access_last_time, a.location, a.is_claim
from (
    select table_id, tbl_name as table_name, db_name as database_name, user_name as owner_id,
        is_partition, lifecycle, filesize, access_last_time, location, is_claim
    from hdp_lbg_supin_zplisting.dwd_zp_rock_table_meta_info
    where dt='${#date(0,0,-1):yyyyMMdd#}' and filesize>0 and status=1
) a
left join (
    select task_id, task_name, queue_name, owner_id, dbtbl, split(dbtbl,'\\.')[0] as database_name, split(dbtbl,'\\.')[1] as table_name
    from (
        select task_id, task_name, table_output,split(table_output,',') as output_tables, hadoop_queue_name as queue_name, user_oa as owner_id
        from hdp_lbg_supin_zplisting.dwd_zp_rock_task_relation
        where dt='${#date(0,0,-1):yyyyMMdd#}'and status = 1
        group by task_id, task_name, table_output, hadoop_queue_name, user_oa
    ) t
    lateral view explode(output_tables) items as dbtbl
) b on a.table_name=b.table_name and a.database_name=b.database_name;


